package physical_spaces.business;

import Entity.DataFile;
import Entity.TempFile;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;
import logical_spaces.business.TableSpaceBusiness;
import physical_spaces.DAO.ManagementDataFileDAO;

/**
 *
 * @author JuanFelipe
 */
public class PhysicalSpaceBusiness {
    
    private ArrayList<DataFile> dataFields;
    private ArrayList<TempFile> tempFields;
    public Connection conn;
    
    
    public PhysicalSpaceBusiness( Connection conn ) {
        this.dataFields = new ArrayList<>();
        this.conn = conn;
    }
    
    public ArrayList<DataFile> getDataFields() {
        return dataFields;
    }

    public void setDataFields(ArrayList<DataFile> dataFields) {
        this.dataFields = dataFields;
    }

    public ArrayList<TempFile> getTempFields() {
        return tempFields;
    }

    public void setTempFields(ArrayList<TempFile> tempFields) {
        this.tempFields = tempFields;
    }
    
    public ArrayList<DataFile> selectDataFiles() {
       this.dataFields = new ArrayList<>();
       String sql="SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES, STATUS, ONLINE_STATUS, INCREMENT_BY FROM DBA_DATA_FILES";
        try{
            ResultSet rs = new ManagementDataFileDAO(conn).executeStatement( sql );
            while( rs.next() ){
                ArrayList<String> stringList = new ArrayList<>();
                for(int i = 1 ; i <= rs.getMetaData().getColumnCount() ; i++){
                    stringList.add( rs.getString(i) );
                    System.out.println(rs.getString(i) );
                }
                
                dataFields.add( new DataFile( stringList.get(0)
                        , stringList.get(1), stringList.get(2)
                        , stringList.get(3), stringList.get(4)
                         ,stringList.get(5), stringList.get(6)) );
               
            }
        } catch ( Exception e ){
            System.out.println("->" + e.getMessage() );
             FacesContext.getCurrentInstance().addMessage(
                    null,
                    new FacesMessage(FacesMessage.SEVERITY_WARN,
                    "Error de Consulta!",
                    "Please Try Again! "+e.getMessage()));
        }
        
        return dataFields;
                
    }
    
    public ArrayList<TempFile> selectTempFiles(){
        
       this.tempFields = new ArrayList<>();
       String sql="SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES, STATUS, INCREMENT_BY FROM DBA_TEMP_FILES";
        try{
            ResultSet rs = new ManagementDataFileDAO(conn).executeStatement( sql );
            while( rs.next() ){
                ArrayList<String> stringList = new ArrayList<>();
                for(int i = 1 ; i <= rs.getMetaData().getColumnCount() ; i++){
                    stringList.add( rs.getString(i) );
                    System.out.println(rs.getString(i) );
                }
                
                tempFields.add( new TempFile( stringList.get(0)
                        , stringList.get(1), stringList.get(2)
                        , stringList.get(3), stringList.get(4)
                         ,stringList.get(5)) );
               
            }
        } catch ( Exception e ){
            System.out.println("->" + e.getMessage() );
             FacesContext.getCurrentInstance().addMessage(
                    null,
                    new FacesMessage(FacesMessage.SEVERITY_WARN,
                    "Error de Consulta!",
                    "Please Try Again! "+e.getMessage()));
        }
        
        return tempFields;
        
    }
    
    public ArrayList<DataFile> selectDataFilesPerTableSpace(String tableSpace) {
        this.dataFields = new ArrayList<>();

        String sql="SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES, STATUS, ONLINE_STATUS, INCREMENT_BY FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = '" + tableSpace + "'";
        try{
            ResultSet rs = new ManagementDataFileDAO(conn).executeStatement( sql );
            while( rs.next() ){
                ArrayList<String> stringList = new ArrayList<>();
                for(int i = 1 ; i <= rs.getMetaData().getColumnCount() ; i++){
                    stringList.add( rs.getString(i) );
                    System.out.println(rs.getString(i) );
                }
                
                dataFields.add( new DataFile( stringList.get(0)
                        , stringList.get(1), stringList.get(2)
                        , stringList.get(3), stringList.get(4)
                         ,stringList.get(5), stringList.get(6)) );
               
            }
        } catch ( Exception e ){
            System.out.println("->" + e.getMessage() );
             FacesContext.getCurrentInstance().addMessage(
                    null,
                    new FacesMessage(FacesMessage.SEVERITY_WARN,
                    "Error de Consulta!",
                    "Please Try Again! "+e.getMessage()));
        }
        
        return dataFields;
        
    }   
    
    public ArrayList<TempFile> selectTempFilesPerTableSpace(String tableSpace) {
        this.tempFields = new ArrayList<>();

        String sql="SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES, STATUS, INCREMENT_BY FROM DBA_TEMP_FILES WHERE TABLESPACE_NAME = '" + tableSpace + "'";
        try{
            ResultSet rs = new ManagementDataFileDAO(conn).executeStatement( sql );
            while( rs.next() ){
                ArrayList<String> stringList = new ArrayList<>();
                for(int i = 1 ; i <= rs.getMetaData().getColumnCount() ; i++){
                    stringList.add( rs.getString(i) );
                    System.out.println(rs.getString(i) );
                }
                
                tempFields.add( new TempFile( stringList.get(0)
                        , stringList.get(1), stringList.get(2)
                        , stringList.get(3), stringList.get(4)
                         ,stringList.get(5)) );
               
            }
        } catch ( Exception e ){
            System.out.println("->" + e.getMessage() );
             FacesContext.getCurrentInstance().addMessage(
                    null,
                    new FacesMessage(FacesMessage.SEVERITY_WARN,
                    "Error de Consulta!",
                    "Please Try Again! "+e.getMessage()));
        }
        
        return tempFields;
        
    }   

    public String reSizeDataFile(String nameDataFile, String newExtendedSize, String unidadDeTamano) {
       
        String query = "ALTER DATABASE DATAFILE '" + nameDataFile + "' RESIZE " + newExtendedSize + unidadDeTamano; 
        try {
            ManagementDataFileDAO physicalSpaceDAO = new ManagementDataFileDAO(conn);
            return physicalSpaceDAO.reSizeDataFile(query);
        } catch (SQLException ex) {
            Logger.getLogger(TableSpaceBusiness.class.getName()).log(Level.SEVERE, null, ex);
            return ex.getMessage();
        }
        
    }
    
    public String reSizeTempFile(String nameTempFile, String newExtendedSize, String unidadDeTamano) {
       
        String query = "ALTER DATABASE TEMPFILE '" + nameTempFile + "' RESIZE " + newExtendedSize + unidadDeTamano; 
        try {
            ManagementDataFileDAO physicalSpaceDAO = new ManagementDataFileDAO(conn);
            return physicalSpaceDAO.reSizeTempFile(query);
        } catch (SQLException ex) {
            Logger.getLogger(TableSpaceBusiness.class.getName()).log(Level.SEVERE, null, ex);
            return ex.getMessage();
        }
        
    }
    
  public String crearQueryEliminarDataFile(String tableSpace,String dataFile){
       
       String query = "ALTER TABLESPACE \"" + tableSpace + "\" DROP DATAFILE '" + dataFile + "'" ;
        try {
            ManagementDataFileDAO physicalSpaceDAO = new ManagementDataFileDAO(conn);
            return physicalSpaceDAO.eliminarDataFile(query);
        } catch (SQLException ex) {
            Logger.getLogger(TableSpaceBusiness.class.getName()).log(Level.SEVERE, null, ex);
            return ex.getMessage();
        }
       
   }
   
   public String crearQueryEliminarTempFile(String tableSpace,String tempFile){
       
       String query = "ALTER TABLESPACE \"" + tableSpace + "\" DROP TEMPFILE '" + tempFile + "'" ;
        try {
            ManagementDataFileDAO physicalSpaceDAO = new ManagementDataFileDAO(conn);
            return physicalSpaceDAO.eliminarTempFile(query);
        } catch (SQLException ex) {
            Logger.getLogger(TableSpaceBusiness.class.getName()).log(Level.SEVERE, null, ex);
            return ex.getMessage();
        }
       
   }
    
}
